----------------------------------------------------------------------------------
--					ARCHIVE MODEL
----------------------------------------------------------------------------------


-- Archives table
create table public.archives (
	id_archive int4 not null,
	archive_date date not null default current_date,
	constraint PK_ARCHIVES primary key(id_archive)
);

-- Archives sequence
create sequence public.archives_seq;


-- dim_survey_date archive
create table public.hist_dim_survey_date (
	id_archive int4 not null,
	id_date int4 not null,
	id_year int2 not null,
	id_month int2 not null,
	id_day int2 not null,
	fulldate date not null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_SURVEY_DATE primary key(id_archive, id_date, id_organization)
);

-- dim_origin_action archive
create table public.hist_dim_origin_action (
	id_archive int4 not null,
	id_action int4 not null,
	action_code varchar(20) not null,
	action_name varchar(150) not null,
	action_description varchar(800) not null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_ORIGIN_ACTION primary key (id_archive, id_action)
);

-- dim_geography archive
create table public.hist_dim_geography (
	id_archive int4 not null,
	id_province varchar(2) not null,
	province_name varchar(30) not null,
	id_council varchar(6) not null,
	council_name varchar(100) not null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_GEOGRAPHY primary key (id_archive, id_council, id_organization)
);

-- dim_council_groups archive
create table public.hist_dim_council_groups (
	id_archive int4 not null,
	id_group int8 not null,
	group_name varchar(40) not null,
	id_council varchar(6) not null,
	council_name varchar(100) not null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_COUNCIL_GROUPS primary key(id_archive, id_group, id_council)
);

-- dim_survey archive
create table public.hist_dim_survey (
	id_archive int4 not null,
	id_survey int4 not null,
	survey_code varchar(20) not null,
	survey_title varchar(60) not null,
	survey_description varchar(200) not null,
	creation_date date not null,
	start_date date not null,
	end_date date not null,
	archived_date date not null,
	id_survey_lifecycle int4 not null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_SURVEY primary key (id_survey)
);

-- dim_demanded_action archive
create table public.hist_dim_demanded_action (
	id_archive int4 not null,
	id_action int4 not null,
	action_code varchar(20) not null,
	action_name varchar(150) not null,
	action_description varchar(800) not null,
	id_unit int8 not null,
	unit_name varchar(50) null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_DEMANDED_ACTION primary key (id_archive, id_action, id_unit)
);


-- dim_area archive
create table public.hist_dim_area (
	id_archive int4 not null,
	id_area int4 not null,
	area_code varchar(20) not null,
	area_description varchar(300) not null,
	id_path int4 not null,
	path_code varchar(20) not null,
	path_description varchar(200) not null,
	id_unit int8 not null,
	unit_name varchar(50) null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_AREA primary key (id_archive, id_path, id_unit)
);



-- dim_qualification archive
create table public.hist_dim_qualification (
	id_archive int4 not null,
	id_qualification int4 not null,
	qualification_code varchar(20) not null,
	qualification_description varchar(300) not null,
	id_unit int8 not null,
	unit_name varchar(50) null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_QUALIFICATION primary key (id_archive, id_qualification, id_unit)
);


-- dim_unit archive
create table public.hist_dim_unit (
	id_archive int4 not null,
	id_unit int8 not null,
	unit_code varchar(20) not null,
	unit_name varchar(50) not null,
	unit_description varchar(1000) not null,
	id_organization int4 not null,
	constraint PK_HIST_DIM_UNIT primary key (id_archive, id_unit)
);


-- fact_survey_answers archive
create table public.hist_fact_survey_answers (
	id_archive int4 not null,
	id_organization int4 not null,
	id_unit int8 not null,
	id_date int4 not null,
	id_survey int4 not null,
	id_council varchar(6) not null,
	id_training_preference int4 not null,
	id_action int4 not null,
	entity_id varchar(9) not null,
	primary key(id_survey, id_unit, entity_id, id_date, id_council)
);

----------------------------------------------------------------------------------
--					ARCHIVE PROCEDURES
----------------------------------------------------------------------------------


create or replace function archive_model() returns void as $$
declare
    num_surveys int4;
		the_archive int4;
		the_archive_date date;
begin
		-- Set date
		the_archive_date := current_date;

		-- Get the number of surveys to archive
    select count(*) as cnt into num_surveys
		from surveys s
		where s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3);

		if num_surveys > 0 then
			-- Generate archive
			the_archive := nextval('archives_seq');
      insert into archives(id_archive) values(the_archive);

	    -- Copy fact survey data
			insert into hist_fact_survey_answers(id_archive, id_organization, id_unit, id_date, id_survey, id_council,
																					 id_training_preference, id_action, entity_id)
			select the_archive, f.id_organization, f.id_unit, f.id_date, f.id_survey, f.id_council,
						 f.id_training_preference, f.id_action, f.entity_id
			from fact_survey_answers f, surveys s
			where f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3);

			--
			-- Only copy dimension data joined with the fact table at the very moment of the archive
			--

			-- Copy dim_survey_date
			insert into hist_dim_survey_date(id_archive, id_date, id_year, id_month, id_day, fulldate, id_organization)
			select the_archive, d.id_date, d.id_year, d.id_month, d.id_day, d.fulldate, d.id_organization
			from dim_survey_date d, fact_survey_answers f, surveys s
			where d.id_date=f.id_date and d.id_organization=f.id_organization and f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by d.id_date, d.id_year, d.id_month, d.id_day, d.fulldate, d.id_organization;

			-- Copy dim_origin_action
			insert into hist_dim_origin_action(id_archive, id_action, action_code, action_name, action_description, id_organization)
			select the_archive, d.id_action, d.action_code, d.action_name, d.action_description, d.id_organization
			from dim_origin_action d, fact_survey_answers f, surveys s
			where d.id_action=f.id_action and f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by d.id_action, d.action_code, d.action_name, d.action_description, d.id_organization;

			-- Copy dim_geography
			insert into hist_dim_geography(id_archive, id_province, province_name, id_council, council_name, id_organization)
			select the_archive, d.id_province, d.province_name, d.id_council, d.council_name, d.id_organization
			from dim_geography d, fact_survey_answers f, surveys s
			where d.id_council=f.id_council and d.id_organization=f.id_organization and f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by d.id_province, d.province_name, d.id_council, d.council_name, d.id_organization;

      -- Copy dim_council_groups
			insert into hist_dim_council_groups(id_archive, id_group, group_name, id_council, council_name, id_organization)
			select the_archive, d.id_group, d.group_name, d.id_council, d.council_name, d.id_organization
			from dim_council_groups d, fact_survey_answers f, surveys s
			where d.id_council=f.id_council and d.id_organization=f.id_organization and f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by d.id_group, d.group_name, d.id_council, d.council_name, d.id_organization;

			-- Copy dim_survey
			insert into hist_dim_survey(id_archive, id_survey, survey_code, survey_title, survey_description,
																	creation_date, start_date, end_date, archived_date,
																	id_survey_lifecycle, id_organization)
			select the_archive, s.id_survey, s.survey_code, s.survey_title, s.survey_description,
						 s.creation_date, s.start_date, s.end_date, s.archived_date, s.id_survey_lifecycle, s.id_organization
			from  fact_survey_answers f, surveys s
			where f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by s.id_survey, s.survey_code, s.survey_title, s.survey_description,
						 	s.creation_date, s.start_date, s.end_date, s.archived_date, s.id_survey_lifecycle, s.id_organization;

			-- Copy dim_demanded_action
			insert into hist_dim_demanded_action(id_archive, id_action, action_code, action_name, action_description,
																					 id_unit, unit_name, id_organization)
			select the_archive, d.id_action, d.action_code, d.action_name, d.action_description,
						 d.id_unit, d.unit_name, d.id_organization
			from dim_demanded_action d, fact_survey_answers f, surveys s
			where d.id_unit=f.id_unit and f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by d.id_action, d.action_code, d.action_name, d.action_description,
						 d.id_unit, d.unit_name, d.id_organization;

			-- Copy dim_area
			insert into hist_dim_area(id_archive, id_area, area_code, area_description, id_path, path_code, path_description,
																id_unit, unit_name, id_organization)
			select the_archive, a.id_area, a.area_code, a.area_description, p.id_path, p.path_code, p.path_description,
						 u.id_unit, null, a.id_organization
			from knowledge_areas a, knowledge_paths p, knowledge_path_units u, fact_survey_answers f, surveys s
			where a.id_area=p.id_area and p.id_path=u.id_path and u.id_unit=f.id_unit and f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by a.id_area, a.area_code, a.area_description, p.id_path, p.path_code, p.path_description,
						 u.id_unit, a.id_organization;

    	-- Copy dim_qualification
			insert into hist_dim_qualification(id_archive, id_qualification, qualification_code, qualification_description,
																				 id_unit, unit_name, id_organization)
			select the_archive, q.id_qualification, q.qualification_code, q.qualification_description,
						 u.id_unit, null, q.id_organization
			from qualifications q, qualification_units u, fact_survey_answers f, surveys s
			where q.id_qualification=u.id_qualification and u.id_unit=f.id_unit and  f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by q.id_qualification, q.qualification_code, q.qualification_description,
						 u.id_unit, q.id_organization;

			-- Copy dim_unit
			insert into hist_dim_unit(id_archive, id_unit, unit_code, unit_name, unit_description, id_organization)
			select the_archive, u.id_unit, u.unit_code, u.unit_name, u.unit_description, u.id_organization
			from knowledge_units u, fact_survey_answers f, surveys s
			where u.id_unit=f.id_unit and f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3)
			group by u.id_unit, u.unit_code, u.unit_name, u.unit_description, u.id_organization;

			--
			-- 	Data removal
			--

			-- Remove fact table data
			delete from fact_survey_answers f using surveys s
			where f.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3);

			-- Remove survey unit answers
			delete from survey_unit_answers su using survey_answer_headers h, surveys s
			where su.id_header=h.id_header and h.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3);
			-- Remove survey answer headers
			delete from survey_answer_headers h using surveys s
			where h.id_survey=s.id_survey and
			s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3);

			-- Update survey data
			update surveys s set id_survey_lifecycle=4
			where s.archived_date <= the_archive_date and s.id_survey_lifecycle in (2,3);

		end if;

end;
$$ language plpgsql;




create or replace function transition_survey_lifecycle() returns void as $$
begin
	-- Put surveys in DESIGN into PRODUCTION when record data is found
	update surveys s set id_survey_lifecycle=2
	from (
  	select id_survey, count(*) as num_answers
		from survey_answer_headers
		group by id_survey
	) a
	where s.id_survey=a.id_survey and s.id_survey_lifecycle=1;

	-- Put surveys in PRODUCTION into CLOSED when date has arrived
	update surveys s set id_survey_lifecycle=3
	where s.end_date <= current_date and id_survey_lifecycle=2;

	-- Do nothing with CLOSED surveys, because archive_model will take care of that
end;
$$ language plpgsql;
